const XLSX = require('xlsx');
const path = require('path');
const fs = require('fs');
const { getCache, setCache } = require('./cacheManager');

const EXCEL_FILE_PATH = path.join(__dirname, '../data/journals.xlsx');

/**
 * 获取Excel文件的最后修改时间
 * @returns {number} 最后修改时间戳
 */
const getExcelFileModifiedTime = () => {
    try {
        const stats = fs.statSync(EXCEL_FILE_PATH);
        return stats.mtimeMs;
    } catch (error) {
        console.error('Error getting file modification time:', error);
        return 0;
    }
};

// Read Excel file and convert to JSON
const readExcelFile = () => {
    try {
        // 尝试从缓存获取数据
        const cachedData = getCache('journals', 'data');
        if (cachedData) {
            return cachedData;
        }

        console.log('[ExcelReader] Reading journals.xlsx file from disk');
        const workbook = XLSX.readFile(EXCEL_FILE_PATH);
        if (!workbook || !workbook.SheetNames || workbook.SheetNames.length === 0) {
            throw new Error('Invalid Excel file format');
        }

        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];

        if (!worksheet) {
            throw new Error('Empty worksheet');
        }

        const data = XLSX.utils.sheet_to_json(worksheet) || [];

        // Add ID to each row if not present
        const processedData = data.map((row, index) => {
            if (!row || typeof row !== 'object') {
                return null;
            }
            return {
                id: row.id || row.ISBN || `row_${index + 1}`,
                ...row
            };
        }).filter(row => row !== null); // Remove any null entries

        // 将数据保存到缓存
        setCache('journals', 'data', processedData);

        return processedData;
    } catch (error) {
        console.error('Error reading Excel file:', error);
        return []; // Return empty array instead of throwing
    }
};

// Get all unique categories from the Excel file
const getCategories = () => {
    try {
        // 尝试从缓存获取分类数据
        const cachedCategories = getCache('journals', 'categories');
        if (cachedCategories) {
            return cachedCategories;
        }

        console.log('[ExcelReader] Computing journal categories');
        const data = readExcelFile();
        const categories = new Set();

        data.forEach(item => {
            if (item.category) {
                categories.add(item.category.toLowerCase());
            }
        });

        const categoriesArray = Array.from(categories).sort();

        // 将分类数据保存到缓存
        setCache('journals', 'categories', categoriesArray);

        return categoriesArray;
    } catch (error) {
        console.error('Error getting categories:', error);
        return [];
    }
};

// 获取所有唯一的出版商及其计数 (从journals.xlsx)
const getPublisherCounts = () => {
    try {
        // 尝试从缓存获取出版商数据
        const cachedPublishers = getCache('journals', 'publishers');
        if (cachedPublishers) {
            return cachedPublishers;
        }

        console.log('[ExcelReader] Computing journal publisher counts');
        const data = readExcelFile();
        const publisherMap = new Map();

        data.forEach(item => {
            if (item.Publisher) {
                const publisher = item.Publisher.trim();
                publisherMap.set(publisher, (publisherMap.get(publisher) || 0) + 1);
            }
        });

        // 转换为数组并按计数降序排序
        const publisherCounts = Array.from(publisherMap.entries())
            .map(([name, count]) => ({ name, count }))
            .sort((a, b) => b.count - a.count);

        // 将出版商数据保存到缓存
        setCache('journals', 'publishers', publisherCounts);

        return publisherCounts;
    } catch (error) {
        console.error('Error getting publisher counts:', error);
        return [];
    }
};

// Search data in Excel file
const searchExcelData = async (query) => {
    try {
        if (!query || typeof query !== 'string') {
            return [];
        }

        const data = await readExcelFile();
        if (!Array.isArray(data)) {
            return [];
        }

        console.log(`[ExcelReader] Searching journals for "${query}"`);
        const searchTerm = query.toLowerCase();

        return data.filter(row => {
            if (!row || typeof row !== 'object') {
                return false;
            }

            // Search in all fields
            return Object.entries(row).some(([key, value]) => {
                // Skip searching in id field
                if (key === 'id' || !value) return false;

                // Convert value to string and search
                const strValue = String(value).toLowerCase();
                return strValue.includes(searchTerm);
            });
        });
    } catch (error) {
        console.error('Error searching Excel data:', error);
        return []; // Return empty array instead of throwing
    }
};

module.exports = {
    readExcelFile,
    searchExcelData,
    getCategories,
    getPublisherCounts
};
